// Do file that puts together the data to be used for the Swedish part of the
// Norwegian-Swedish study on birth order


clear
set more off


// Save birth info for later use
  use LopNr FodArMan UtlSvBakG Kon using "D:\SCB_ConPol\Stata\RTB\Fodelseuppg.dta", clear 
  bysort LopNr: egen nocc = count(LopNr)
  keep if nocc==1
  drop nocc
  tempfile FodUppg 
  save `FodUppg'

// Save death dates for later use
  use "D:\SCB_ConPol\Stata\RTB\DodDat.dta", replace
  bysort LopNr: egen nocc = count(LopNr)
  keep if nocc==1
  drop nocc

  tempfile DodDat 
  save `DodDat'

// Save data on turnout in 2009 (EP election) and 2010 (national election) for later use
  use "D:\SCB_ConPol\Stata\VD\Valdelt_2010.dta", clear
  bysort LopNr: egen nocc = count(LopNr)
  keep if nocc==1
  drop nocc
  
  gen RostRI = .
  replace RostRI = 0 if r==1
  replace RostRI = 1 if r==2
  replace RostRI = 1 if inrange(r, 4, 6)
  keep LopNr RostRI
  
  gen Ar = 2010
  tempfile VD10
  save `VD10'

  use "D:\SCB_ConPol\Stata\VD\Valdelt_09.dta", clear
  bysort LopNr: egen nocc = count(LopNr)
  keep if nocc==1
  drop nocc
  gen RostEU = .
  replace RostEU = 0 if e==1
  replace RostEU = 1 if e==2
  replace RostEU = 1 if inlist(e, 4, 6)
  keep LopNr RostEU 
	
  gen Ar = 2009
  tempfile VD09
  save `VD09'


// Create indicator for being adopteed
  use "D:\SCB_ConPol\Stata\FlerGen\AdForaldrar.dta", clear
  keep LopNr LopNrAdMor LopNrAdFar
  
  gen Adopterad = 1 
  bysort LopNr: gen occ=_n
  keep if occ==1
  
  keep LopNr Adopterad
  tempfile AdBarn
  save `AdBarn'

// Save info on SES from censuses and LISA waves for later use
preserve
run "C:\Userdata\Shared\Dofiles\DoData\SiblingOrder\Replication do-files\Replication - SESData7090.do"
run "C:\Userdata\Shared\Dofiles\DoData\SiblingOrder\Replication do-files\Replication - SESData0610.do"
restore

// Save info on parental education for later use
  use "D:\Data\ProjData\SiblingOrder\SESData7090.dta", clear

  collapse (mean) UtbNiva_1970 UtbNiva_1990, by(LopNr)

  tempfile SEStid
  save `SEStid'
  
  use "D:\Data\ProjData\SiblingOrder\SESData0610.dta", clear
  
  keep if Ar<2009
  
  collapse (max) Sun2000niva, by(LopNr)
  
  merge 1:1 LopNr using `SEStid'

  gen FarUtbAr_70=7 if UtbNiva_1970==1
  replace FarUtbAr_70=9 if UtbNiva_1970==2
  replace FarUtbAr_70=11 if UtbNiva_1970==3
  replace FarUtbAr_70=12 if UtbNiva_1970==4
  replace FarUtbAr_70=14 if UtbNiva_1970==5
  replace FarUtbAr_70=15.5 if UtbNiva_1970==6
  replace FarUtbAr_70=19 if UtbNiva_1970==7
  
  gen MorUtbAr_70 = FarUtbAr_70
  
  gen FarUtbAr_Max=7 if Sun2000niva<200
  replace FarUtbAr_Max=9 if inrange(Sun2000niva,200,299)
  replace FarUtbAr_Max=9.5 if Sun2000niva==204
  replace FarUtbAr_Max=10 if inrange(Sun2000niva,310,319)
  replace FarUtbAr_Max=11 if inrange(Sun2000niva,320,329)
  replace FarUtbAr_Max=12 if inrange(Sun2000niva,330,339)
  replace FarUtbAr_Max=13 if inrange(Sun2000niva,410,419)
  replace FarUtbAr_Max=14 if inrange(Sun2000niva,520,529)
  replace FarUtbAr_Max=15 if inrange(Sun2000niva,530,539)
  replace FarUtbAr_Max=16 if inrange(Sun2000niva,540,549)
  replace FarUtbAr_Max=17 if inrange(Sun2000niva,550,559)
  replace FarUtbAr_Max=18 if inrange(Sun2000niva,600,629)
  replace FarUtbAr_Max=19 if inrange(Sun2000niva,640,649)
  
  gen MorUtbAr_Max = FarUtbAr_Max
  
  gen MorUtbAr = max(MorUtbAr_Max, MorUtbAr_70)
  gen FarUtbAr = max(FarUtbAr_Max, FarUtbAr_70)	

  keep LopNr MorUtbAr FarUtbAr
	
  merge m:1 LopNr using `FodUppg', keepusing(FodArMan)
  keep if _merge == 3
  drop _merge 
  
  gen FodAr = floor(FodArMan/100)	

  keep LopNr FarUtbAr MorUtbAr
  
  tempfile ForUtb
  save `ForUtb'
  
// Start to merge all the data
  use "D:\SCB_ConPol\Stata\FlerGen\BioForaldrar.dta", clear
  rename lopnrmor LopNrMor
  rename lopnrfar LopNrFar
  rename morantbarn MorAntBarn
  rename farantbarn FarAntBarn
  rename morordnr MorOrdNr
  rename farordnr FarOrdNr
	
  bysort LopNrMor: egen mOrdNr = min(MorOrdNr)
  drop if mOrdNr == 0
  drop if MorOrdNr == 0
  
  bysort LopNr: egen nocc = count(LopNr)
  keep if nocc==1
  drop nocc

  merge 1:1 LopNr using `FodUppg'
  keep if _merge == 3
  drop _merge

  merge 1:1 LopNr using `DodDat'
  drop if _merge==2
  drop _merge 
	
  expand 2
  bysort LopNr: gen occ = _n
  generate Ar = 2009 if occ == 1
  replace Ar  = 2010 if occ==2
  drop occ
			 
  merge 1:1 LopNr Ar using `VD10'
  drop if _merge==2
  drop _merge 
	
  merge 1:1 LopNr Ar using `VD09'
  drop if _merge==2
  drop _merge 
	
  merge m:1 LopNr using `AdBarn'
  drop if _merge==2
  drop _merge
	
  drop if Ar > floor(DodDatum/10000)	
  compress
	
  gen FodAr = floor(FodArMan/100)
  drop if MorOrdNr == 0
	
  bysort LopNrMor Ar FodArMan: egen Tvilling = count(LopNr)
  bysort LopNrMor: egen FamTvilling = max(Tvilling>1) if Tvilling<.
	
// Merge on parental birth information and voter turnout
	gen aLopNr = LopNr 
	rename FodArMan aFodArMan
	rename RostRI aRostRI
	rename RostEU aRostEU
 	replace LopNr = LopNrMor 
	
	merge m:1 LopNr using `FodUppg', keepusing(FodArMan)
	drop if _merge==2 
	drop _merge 
	rename FodArMan MorFodArMan
	
	merge m:1 LopNr using `ForUtb', keepusing(LopNr MorUtbAr)
	drop if _merge==2 
	drop _merge 
	
	replace LopNr = LopNrFar
	merge m:1 LopNr using `FodUppg', keepusing(FodArMan)
	drop if _merge==2 
	drop _merge 
	rename FodArMan FarFodArMan
	
	merge m:1 LopNr using `ForUtb', keepusing(LopNr FarUtbAr)
	drop if _merge==2 
	drop _merge 
	
	drop LopNr
	rename aLopNr LopNr
	
	gen MorFodAr = floor(MorFodArMan/100)
	gen FarFodAr = floor(FarFodArMan/100)
	
	drop MorFodArMan FarFodArMan
	
	rename aRostRI RostRI
	rename aRostEU RostEU
	rename aFodArMan FodArMan
	
	
// Add data on earnings and education percentiles	
 preserve 
  use "D:\Data\ProjData\SiblingOrder\SESData0610.dta", clear
  
  gen UtbAr=7 if Sun2000niva<200
  replace UtbAr=9 if inrange(Sun2000niva,200,299)
  replace UtbAr=9.5 if Sun2000niva==204
  replace UtbAr=10 if inrange(Sun2000niva,310,319)
  replace UtbAr=11 if inrange(Sun2000niva,320,329)
  replace UtbAr=12 if inrange(Sun2000niva,330,339)
  replace UtbAr=13 if inrange(Sun2000niva,410,419)
  replace UtbAr=14 if inrange(Sun2000niva,520,529)
  replace UtbAr=15 if inrange(Sun2000niva,530,539)
  replace UtbAr=16 if inrange(Sun2000niva,540,549)
  replace UtbAr=17 if inrange(Sun2000niva,550,559)
  replace UtbAr=18 if inrange(Sun2000niva,600,629)
  replace UtbAr=19 if inrange(Sun2000niva,640,649)
	
  merge m:1 LopNr using `FodUppg', keepusing(FodArMan)
  keep if _merge == 3
  drop _merge 
  
  gen FodAr = floor(FodArMan/100)
  
  bysort LopNr: egen LoneInk0608 = mean(LoneInk) if inrange(Ar, 2006, 2008)
  bysort LopNr: egen LoneInk0709 = mean(LoneInk) if inrange(Ar, 2007, 2009)

  
  gen UtbAr09 = UtbAr if Ar == 2009
  gen UtbAr10 = UtbAr if Ar == 2010
  
  collapse (mean) FodAr LoneInk0608 LoneInk0709 UtbAr09 UtbAr10, by(LopNr)
  
 // Create precentile ranks by cohort
  by FodAr, sort: egen n = count(LoneInk0608)
  by FodAr: egen i = rank(LoneInk0608), track 
  gen pLoneInk0608 = (i - 1) / (n - 1) 
  drop i n
  
  by FodAr, sort: egen n = count(LoneInk0709)
  by FodAr: egen i = rank(LoneInk0709), track 
  gen pLoneInk0709 = (i - 1) / (n - 1)  
  drop n i 
  
  by FodAr, sort: egen n = count(UtbAr09)
  by FodAr: egen i = rank(UtbAr09), track 
  gen pUtbAr09 = (i - 1) / (n - 1)  
  drop n i
  
  by FodAr, sort: egen n = count(UtbAr10)
  by FodAr: egen i = rank(UtbAr10), track 
  gen pUtbAr10 = (i - 1) / (n - 1)  
  drop n i
  
  tempfile LonDat
  save `LonDat'
 restore
 
   merge m:1 LopNr using `LonDat'
   drop if _merge==2
   drop _merge
  
  compress
  
// Add data on divorces 
preserve  
 foreach num of numlist 1968/2014 {
 
   use D:\SCB_ConPol\Stata\RTB\RTB_`num'.dta, clear
   keep LopNr Civil
   gen Ar = `num'
   
   if (`num'>1997) {
     rename Civil sCivil
   }
   
   compress
   tempfile RTB`num'
   save `RTB`num''
 }
 
 use `RTB1968', clear
 
 foreach num of numlist 1969/2014 {
   append using `RTB`num''
 }
 
 gen Skild = Civil == 4 | trim(sCivil) == "S"
 gen Skild_Ar = Ar if Skild == 1
 
 bysort LopNr: egen totSkildAr = total(Skild)
 bysort LopNr: egen totAr = count(Skild)
 bysort LopNr: egen minSkildAr = min(Skild_Ar) 
 bysort LopNr: egen maxSkildAr = max(Skild_Ar)
 bysort LopNr: egen mSkild = max(Skild)
 bysort LopNr: gen occ = _n

 drop Skild
 rename mSkild Skild
 keep if occ == 1
 
 keep LopNr Skild minSkild maxSkild
 tempfil SkildData
 save `SkildData'
restore

  rename LopNr aLopNr

  rename LopNrFar LopNr
  merge m:1 LopNr using `SkildData'
  drop if _merge == 2
  drop _merge

  rename Skild SkildFar
  rename minSkildAr minSkildArFar
  rename maxSkildAr maxSkildArFar
  
  rename LopNr LopNrFar

  rename LopNrMor LopNr
  merge m:1 LopNr using `SkildData'
  drop if _merge == 2

  rename Skild SkildMor
  rename minSkildAr minSkildArMor
  rename maxSkildAr maxSkildArMor
  rename LopNr LopNrMor
  rename aLopNr LopNr
  
  drop _merge
  
 // Keep only relevant variables and drop SES datasets
  drop mOrdNr FodArMan DodDatum Tvilling LoneInk0608 LoneInk0709 minSkildArFar maxSkildArFar minSkildArMor maxSkildArMor FarAntBarn
 
  erase "D:\Data\ProjData\SiblingOrder\SESData7090.dta"
  erase "D:\Data\ProjData\SiblingOrder\SESData0610.dta"
 
  save "D:\Data\ProjData\SiblingOrder\ReplicationSwedishData 191129.dta", replace
		
